package com.len.service.impl;

import com.len.config.PrestoDataSource;
import com.len.entity.Campaign;
import com.len.service.CampaignService;
import com.len.service.MarketMonitorService;
import com.len.util.ReType;
import com.len.utils.DateUtil;
import com.len.utils.Tools;
import com.len.vo.ParameterInfo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author songxiangyang
 * @date 2019/1/17
 */
@Service
public class MarketMonitorServiceImpl implements MarketMonitorService {

    @Autowired
    private PrestoDataSource prestoDataSource;
    @Autowired
    private CampaignService channelService;

    @Override
    public ReType loadKeyWordTableData(ParameterInfo info) {
        ReType reType = new ReType();
        // 动态拼接
        Object objs = info.getAjax_data();
        String str = objs.toString().replace("\"", "");
        String strm = str.replace("[", "");
        String strs = strm.replace("]", "");
        String[] arr = strs.split(",");
        StringBuffer selectStr = new StringBuffer();
        StringBuffer groupByStr = new StringBuffer();
        StringBuffer groupByStrBase = new StringBuffer();
        List<String> sortList = new ArrayList<>();
        StringBuffer isLeftJoin = new StringBuffer();

        groupByStrBase.append(",");
        for (int i = 0; i < arr.length; i++) {
            if ("dt".equals(arr[i])) {
                selectStr.append("ot.").append(arr[i]).append(",");
                groupByStr.append("ot.").append(arr[i]).append(",");
                groupByStrBase.append("a.").append(arr[i]).append(",");
                sortList.add("ot." + arr[i]);
            } else if ("keyword".equals(arr[i])) {
                selectStr.append("ot.").append(arr[i]).append(",");
                groupByStr.append("ot.").append(arr[i]).append(",");
                groupByStrBase.append("a.").append(arr[i]).append(",");
                sortList.add("ot." + arr[i]);
            } else if ("adgroup_name".equals(arr[i])) {
                selectStr.append("ot.").append(arr[i]).append(",");
                groupByStr.append("ot.").append(arr[i]).append(",");
                groupByStrBase.append("a.").append(arr[i]).append(",");
                sortList.add("ot." + arr[i]);
            } else if ("campaign_name".equals(arr[i])) {
                selectStr.append("ot.").append(arr[i]).append(",");
                groupByStr.append("ot.").append(arr[i]).append(",");
                groupByStrBase.append("a.").append(arr[i]).append(",");
                sortList.add("ot." + arr[i]);
            } else if ("cmpid".equals(arr[i])) {
                selectStr.append("ot.").append(arr[i]).append(",");
                groupByStr.append("ot.").append(arr[i]).append(",");
                groupByStrBase.append("a.cmpid").append(",");
                sortList.add("ot." + arr[i]);
            } else if ("agent_company".equals(arr[i])) {
                selectStr.append("dd.channel_cn as dd").append(",");
                groupByStr.append("dd.channel_cn").append(",");
                groupByStrBase.append("a.agent_company").append(",");
                sortList.add("dd.channel_cn");
            } else if ("media_type".equals(arr[i])) {
                selectStr.append("d.channel_cn as d").append(",");
                groupByStr.append("d.channel_cn").append(",");
                groupByStrBase.append("a.media_type").append(",");
                sortList.add("d.channel_cn");
            } else if ("media_channel".equals(arr[i])) {
                selectStr.append("c.channel_cn as c").append(",");
                groupByStr.append("c.channel_cn").append(",");
                groupByStrBase.append("a.media_channel").append(",");
                sortList.add("c.channel_cn");
            } else if ("campaign_account".equals(arr[i])) {
                selectStr.append("ddd.channel_cn as ddd").append(",");
                groupByStr.append("ddd.channel_cn").append(",");
                groupByStrBase.append("a.campaign_account").append(",");
                sortList.add("ddd.channel_cn");
            } else if ("campaign_word_type".equals(arr[i])) {
                selectStr.append("dddd.channel_cn as dddd").append(",");
                groupByStr.append("dddd.channel_cn").append(",");
                groupByStrBase.append("a.campaign_word_type").append(",");
                sortList.add("dddd.channel_cn");
            } else if ("campaign_insurance_type".equals(arr[i])) {
                selectStr.append("ddddd.channel_cn as ddddd").append(",");
                groupByStr.append("ddddd.channel_cn").append(",");
                groupByStrBase.append("a.campaign_insurance_type").append(",");
                sortList.add("ddddd.channel_cn");
            } else if ("impression".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.impression),0) as impression").append(",");
                sortList.add("COALESCE(sum(ot.impression),0)");
            } else if ("click".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.click),0) as click").append(",");
                sortList.add("COALESCE(sum(ot.click),0)");
            } else if ("avg_click_count".equals(arr[i])) {
                selectStr.append("if(sum(ot.impression)=0,0,COALESCE(ROUND(sum(ot.click)/sum(ot.impression)*100,2),0)) as avg_click_count").append(",");
                sortList.add("if(sum(ot.impression)=0,0,COALESCE(ROUND(sum(ot.click)/sum(ot.impression)*100,2),0))as avg_click_cost");
            } else if ("avg_click_cost".equals(arr[i])) {
                selectStr.append("if(sum(ot.click)=0,0,COALESCE(ROUND(sum(ot.cost)/sum(ot.click),2),0)) as avg_click_cost").append(",");
                sortList.add("if(sum(ot.click)=0,0,COALESCE(ROUND(sum(ot.cost)/sum(ot.click),2),0))");
            } else if ("cost".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.cost),0) as cost").append(",");
                sortList.add("COALESCE(sum(ot.cost),0)");
            } else if ("page_views".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.page_views),0) as page_views").append(",");
                sortList.add("COALESCE(sum(ot.page_views),0)");
            } else if ("visits".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.visits),0) as visits").append(",");
                sortList.add("COALESCE(sum(ot.visits),0)");
            } else if ("avg_visits_cost".equals(arr[i])) {
                selectStr.append("if(sum(ot.visits)=0,0,COALESCE(ROUND(sum(ot.cost)/sum(ot.visits),2),0)) as avg_visits_cost").append(",");
                sortList.add("if(sum(ot.visits)=0,0,COALESCE(ROUND(sum(ot.cost)/sum(ot.visits),2),0))");
            } else if ("visitors".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.visitors),0) as visitors").append(",");
                sortList.add("COALESCE(sum(ot.visitors),0)");
            } else if ("registertion_count".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.registertion_count),0) as registertion_count").append(",");
                sortList.add("COALESCE(sum(ot.registertion_count),0)");
            } else if ("car_insurance_visits".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.car_insurance_visits),0) as car_insurance_visits").append(",");
                sortList.add("COALESCE(sum(ot.car_insurance_visits),0)");
            } else if ("none_car_insurance_visits".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.none_car_insurance_visits),0) as none_car_insurance_visits").append(",");
                sortList.add("COALESCE(sum(ot.none_car_insurance_visits),0)");
            } else if ("name_lists".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.name_lists),0) as name_lists").append(",");
                sortList.add("COALESCE(sum(ot.name_lists),0)");
            } else if ("avg_name_lists_cost".equals(arr[i])) {
                selectStr.append("if(sum(ot.name_lists)=0,0,COALESCE(ROUND(sum(ot.cost)/sum(ot.name_lists),2),0)) as avg_name_lists_cost").append(",");
                sortList.add("if(sum(ot.name_lists)=0,0,COALESCE(ROUND(sum(ot.cost)/sum(ot.name_lists),2),0))");
            } else if ("name_lists_rate".equals(arr[i])) {
                selectStr.append("if(sum(ot.visits)=0,0,COALESCE(ROUND(sum(ot.name_lists)/sum(ot.visits)*100,2),0)) as name_lists_rate").append(",");
                sortList.add("if(sum(ot.visits)=0,0,COALESCE(ROUND(sum(ot.name_lists)/sum(ot.visits)*100,2),0))");
            } else if ("avg_visits_time".equals(arr[i])) {
                selectStr.append("if(sum(ot.visits)=0,0,COALESCE(ROUND(sum(ot.visits_duration)/sum(ot.visits),2),0)) as avg_visits_time").append(",");
                sortList.add("if(sum(ot.visits)=0,0,COALESCE(ROUND(sum(ot.visits_duration)/sum(ot.visits),2),0))");
            } else if ("bounces_rate".equals(arr[i])) {
                selectStr.append("if(sum(ot.entries)=0,0,COALESCE(ROUND(sum(ot.bounces)/sum(ot.entries)*100,2),0)) as bounces_rate").append(",");
                sortList.add("if(sum(ot.entries)=0,0,COALESCE(ROUND(sum(ot.bounces)/sum(ot.entries)*100,2),0))");
            } else if ("car_order".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.car_order),0) as car_order").append(",");
                sortList.add("COALESCE(sum(ot.car_order),0)");
            } else if ("nocar_order".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.nocar_order),0) as nocar_order").append(",");
                sortList.add("COALESCE(sum(ot.nocar_order),0)");
            } else if ("orders".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.orders),0) as orders").append(",");
                sortList.add("COALESCE(sum(ot.orders),0)");
            } else if ("orders_rate".equals(arr[i])) {
                selectStr.append("if(sum(ot.name_lists)=0,0,COALESCE(ROUND(sum(ot.orders)/sum(ot.name_lists)*100,2),0)) as orders_rate").append(",");
                sortList.add("if(sum(ot.name_lists)=0,0,COALESCE(ROUND(sum(ot.orders)/sum(ot.name_lists)*100,2),0))");
            } else if ("car_revenue".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.car_revenue),0) as car_revenue").append(",");
                sortList.add("COALESCE(sum(ot.car_revenue),0)");
            } else if ("nocar_revenue".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.nocar_revenue),0) as nocar_revenue").append(",");
                sortList.add("COALESCE(sum(ot.nocar_revenue),0)");
            } else if ("revenue".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.revenue),0) as revenue").append(",");
                sortList.add("COALESCE(sum(ot.revenue),0)");
            } else if ("revenue_cost".equals(arr[i])) {
                selectStr.append("if(sum(ot.cost)=0,0,COALESCE(ROUND(sum(ot.revenue)/sum(ot.cost),2),0)) as revenue_cost").append(",");
                sortList.add("if(sum(ot.cost)=0,0,COALESCE(ROUND(sum(ot.revenue)/sum(ot.cost),2),0))");
            } else if ("actual_order".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.actual_order),0) as actual_order").append(",");
                sortList.add("COALESCE(sum(ot.actual_order),0)");
            } else if ("actual_order_rate".equals(arr[i])) {
                selectStr.append("if(sum(ot.name_lists)=0,0,COALESCE(ROUND(sum(ot.actual_order)/sum(ot.name_lists)*100,2),0)) as actual_order_rate").append(",");
                sortList.add("if(sum(ot.name_lists)=0,0,COALESCE(ROUND(sum(ot.actual_order)/sum(ot.name_lists)*100,2),0))");
            } else if ("actual_revenue".equals(arr[i])) {
                selectStr.append("COALESCE(sum(ot.actual_revenue),0) as actual_revenue").append(",");
                sortList.add("COALESCE(sum(ot.actual_revenue),0)");
            }
        }

        if (groupByStr.toString().length() > 0) {
            groupByStr.deleteCharAt(groupByStr.length() - 1);
        } else {
            groupByStr.append("keyword,adgroup_name,campaign_name,media_channel");
        }
        if (selectStr.toString().length() > 0) {
            selectStr.deleteCharAt(selectStr.length() - 1);
        }
        if (groupByStrBase.toString().length() > 0) {
            groupByStrBase.deleteCharAt(groupByStrBase.length() - 1);
        }
        if (isLeftJoin.toString().length() == 0) {
            isLeftJoin.append("join");
        }

        String[] times = DateUtil.dateTransform(info.getDateStr());
        String startDate = times[0];
        String endDate = times[1];
        String equipmentType = "0".equals(info.getEquipmentType()) ? null : info.getEquipmentType();
        String mediaCompany = "0".equals(info.getMediaCompany()) ? null : info.getMediaCompany();
        String account = "0".equals(info.getAccountType()) ? null : info.getAccountType();
        String insuranceType = "0".equals(info.getInsuranceType()) ? null : info.getInsuranceType();
        mediaCompany = Tools.spilt(mediaCompany);
        account = Tools.spilt(account);
        insuranceType = Tools.spilt(insuranceType);


//        String whereDateStr =
//                " create_date >= '" + startDate + "' and create_date<='" + endDate + "' ";
//
//        String whereKeywordStr =
//                " dt >= '" + startDate + "' and dt<='" + endDate + "' "
//                        + ((equipmentType == null) ? "" : " and f.device = " + equipmentType + "")
//                        + (("'null'".equals(mediaCompany)) ? "" : " and f.media_channel in (" + mediaCompany + ")")
//                        + (("'null'".equals(account)) ? "" : " and f.campaign_account in (" + account + ")")
//                        + (("'null'".equals(insuranceType)) ? "" : " and f.campaign_insurance_type in (" + insuranceType + ")");

        String whereReportStr =
                " a.dt >= '" + startDate + "' and a.dt<='" + endDate + "' "
                        + ((equipmentType == null) ? "" : " and a.equipmentType = '" + equipmentType + "'")
                        + ((StringUtils.isBlank(mediaCompany)) ? "" : " and a.media_channel in(" + mediaCompany + ")")
                        + ((StringUtils.isBlank(account)) ? "" : " and a.campaign_account in (" + account + ")")
                        + ((StringUtils.isBlank(insuranceType)) ? "" : " and a.campaign_insurance_type in (" + insuranceType + ")");

//        String dateSql = "(                                                       \n" +
//                "        SELECT                                                  \n" +
//                "           create_date,                                       \n" +
//                "           date_format(create_date,'%Y-%m-%d') as days,                    \n" +
//                "           CONCAT(YEAR(create_date),' ',WEEKOFYEAR(create_date),'周') as weeks,\n" +
//                "           DATE_FORMAT(create_date,'%Y-%m月') as months,\n" +
//                "           DATE_FORMAT(create_date,'%Y年') as years\n" +
//                "       FROM                                             \n" +
//                "           picc.vi_date                                  \n" +
//                "       WHERE \n" +whereDateStr+
//                "       GROUP BY                       \n" +
////                "           days\n" +
//                info.getDateType() +
//                ") dl \n" +
//                "left join\n";

        String mainSql = "(\n" +
                "SELECT\n" +
                " a.dt," +
                "\ta.keyword as keyword,\n" +
                "\ta.cmpid as cmpid,\n" +
                "\ta.adgroup_name as adgroup_name,\n" +
                "\ta.campaign_name as campaign_name,\n" +
                "\ta.agent_company as agent_company,\n" +
                "  a.media_type as media_type,\n" +
                "  a.media_channel as media_channel,\n" +
                "  a.campaign_account as campaign_account,\n" +
                "  a.campaign_word_type as campaign_word_type,\n" +
                "  a.campaign_insurance_type as campaign_insurance_type," +
                "\tCOALESCE(sum(a.impression),0) as impression,\n" +
                "\tCOALESCE(sum(a.click),0) as click,\n" +
                "\tCOALESCE(sum(a.cost),0) as cost,\n" +
                "\tCOALESCE(sum(a.page_views),0) as page_views,\n" +
                "\tCOALESCE(sum(a.visits),0) as visits,\n" +
                "\tCOALESCE(sum(a.visitors),0) as visitors,\n" +
                "\tCOALESCE(sum(a.registertion_count),0) as registertion_count,\n" +
                "\tCOALESCE(sum(a.car_insurance_visits),0) as car_insurance_visits,\n" +
                "\tCOALESCE(sum(a.none_car_insurance_visits),0) as none_car_insurance_visits,\n" +
                "\tCOALESCE(sum(a.name_lists),0) as name_lists,\n" +
                "    COALESCE(sum(a.visits_duration),0) as visits_duration, \n" +
                "    COALESCE(sum(a.bounces),0) as bounces, \n" +
                "    COALESCE(sum(a.entries),0) as entries, \n" +
                "\tCOALESCE(sum(a.car_order),0) as car_order, \n" +
                "\tCOALESCE(sum(a.nocar_order),0) as nocar_order, \n" +
                "\tCOALESCE(sum(a.orders),0) as orders, \n" +
                "\tCOALESCE(sum(a.car_revenue),0) as car_revenue,\n" +
                "\tCOALESCE(sum(a.nocar_revenue),0) as nocar_revenue,\n" +
                "\tCOALESCE(sum(a.revenue),0) as revenue,\n" +
                "\tCOALESCE(sum(a.actual_order),0) as actual_order,\n" +
                "\tCOALESCE(sum(a.actual_revenue),0) as actual_revenue" +
                "\t FROM\n" +
                " picc.mm_keyword_market_campaign_all a" +
                "\t where \n" + whereReportStr +
                "\t group by a.dt,a.keyword,a.cmpid,a.adgroup_name,a.campaign_name,a.agent_company,\n" +
                "\ta.media_type,a.media_channel,a.campaign_account,a.campaign_word_type,a.campaign_insurance_type\n" +
                ") ot\n";

//        String dateLeftJoinSql = "ON dl."+info.getDateType()+" = ot."+info.getDateType()+"\n";

        String leftJoinSql =
                "LEFT JOIN picc.sys_channel dd\n" +
                        "ON dd.channel_type='AGENT_COMPANY' AND ot.agent_company=dd.channel_en\n" +
                        "LEFT JOIN picc.sys_channel d\n" +
                        "ON d.channel_type='MEDIA_TYPE' AND ot.media_type=d.channel_en\n" +
                        "LEFT JOIN picc.sys_channel c\n" +
                        "ON c.channel_type='MEDIA_CHANNEL' AND ot.media_channel=c.channel_en\n" +
                        "LEFT JOIN picc.sys_channel ddd\n" +
                        "ON ddd.channel_type='ACCOUNT_TYPE' AND ot.campaign_account=ddd.channel_en\n" +
                        "LEFT JOIN picc.sys_channel dddd\n" +
                        "ON dddd.channel_type='PRODUCT_TYPE' AND ot.campaign_word_type=dddd.channel_en\n" +
                        "LEFT JOIN picc.sys_channel ddddd\n" +
                        "ON ddddd.channel_type='INSURANCE_TYPE' AND ot.campaign_insurance_type=ddddd.channel_en" +
                        " group by " + groupByStr;

        String headerSql = "select \n" +
                selectStr +
                " from \n";

        String sql = headerSql + mainSql + leftJoinSql + " limit 5000";


        Integer sortColumnIndex = info.getSortColumnIndex();
        String sortType = info.getSortType();
        if (sortColumnIndex != null) {
            String sortColumn = sortList.get(sortColumnIndex);
            sql += " ORDER BY " + sortColumn + " " + sortType;
        }
//        List<Map<Object, Object>> allData = prestoDataSource.getAllData(sql);
//        reType.setCount(allData == null || allData.size() < 0 ? 0L : allData.size());
//        reType.setData(allData);
        return reType;
    }

    @Override
    public ReType loadsDictionaryByType(Campaign channel) {
        ReType reType = new ReType();
        List<Map<String, String>> data = new ArrayList<>();
        Map<String, String> map = new HashMap();
        List<Campaign> channels = channelService.select(channel);
        channels.forEach(c ->{
            String channelEn = c.getChannelEn();
            String channelCn = c.getChannelCn();
            map.put(channelEn, channelCn);
        });
        data.add(map);
        reType.setData(data);
        return reType;
    }
}
